﻿-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Linh DUong>
-- Create date: <Create Date,,>
-- Description:	<Tìm kiếm thông tin>
-- =============================================
alter PROCEDURE [dbo].[Sps_Search]
(
	@person_code nvarchar(max),
	@first_name nvarchar(200),
	@middle_name nvarchar(200),
	@lastname nvarchar(200),
	@birthday varchar(10),
	@death_day varchar(10)
)
	
AS
BEGIN
	declare  @select nvarchar(4000)
	declare @where  nvarchar(4000)

    -- Insert statements for procedure here
	set @select='select person_code,first_name,middle_name,last_name,birthday,death_day
	 from tb_fa_person '

	 set @where=' where 1=1'

	 if(@person_code <>'')
	 begin 
		set @where=@where+'and person_code like  ''%'+@person_code+'%'''
	 end
	 
	 if(@first_name <>'')
	 begin 
		set @where=@where+' and first_name like N''%'+@first_name+'%'''
	 end
	 
	if(@middle_name <>'')
	 begin 
		set @where=@where+'and middle_name like N''%'+@middle_name+'%'''
	 end	 
	 if(@lastname <>'')
	 begin 
		set @where=@where+'and last_name like N''%'+@lastname+'%'''
	 end

	 if(@birthday <>'')
	 begin 
		set @where=@where+'and birthday like ''%'+@birthday+'%'''
	 end	
	 
	 if(@death_day <>'')
	 begin 
		set @where=@where+'and death_day like ''%'+@death_day+'%'''
	 end	 	  
	-- print (@select+@where)
	 exec (@select+@where)
END
GO
